package ca.ubc.cs304.library.actions;



import java.sql.*;

import java.io.*;

import ca.ubc.cs304.library.sql.OracleDatabaseWrapper;


public class LibrarianActions
{


	/**
	 * Run before each query to ensure oracle is connected :-)
	 * @param db the OracleDatabaseWrapper (connected or otherwise).
	 * @throws Exception in case oracle is not connected to.
	 */
	private static void connectionCheck (OracleDatabaseWrapper db) throws Exception {
		if (db == null || !db.isConnected()) 
			throw new Exception ("Not connected to Database");
	}

	/**
	 * @param  oracle database wrapper
	 * @param all data associated with book table
	 * @param a string array of subjects to add to the hassubject table
	 * @
	 * 
	 */ 

 public static ResultSet insertBook(OracleDatabaseWrapper db, String isbn, String title, String mainAuthor, String publisher, int year2, String [] subject) throws Exception
 {
	connectionCheck(db);	 
	String year = Integer.toString(year2);
	String callnum ="Q " + isbn + " " + year;
	PreparedStatement insbook;
	PreparedStatement inscopy;
	PreparedStatement inssubject;
	Statement  stmt;	
	ResultSet rs =null;
	try{
	  insbook = db.createPreparedStatement("INSERT INTO BOOK VALUES (?,?,?,?,?,?)");
	  inscopy = db.createPreparedStatement("INSERT INTO BOOKCOPY VALUES (?, ?, ? )");
	  inssubject = db.createPreparedStatement("INSERT INTO HasSubject VALUES(?, ?)");
		ResultSet  rs1;
		int count=0;
		  stmt = db.createStatement();
		  rs1 = stmt.executeQuery("SELECT COUNT(*) AS COUNT FROM bookcopy WHERE callnumber="+ "\'" + callnum+ "\'");
		  while(rs1.next())
		  {
		      count = rs1.getInt("COUNT");
		  }
		  rs1.close(); 
	      if (count==0){
	    	  if(year.length() !=4){
	    		  insbook.setNull(6, java.sql.Types.INTEGER);
	    	  }
	    	  else {
	    		  insbook.setInt(6, year2);
	    	  }
	    	  insbook.setString(2, isbn);  
	    	  insbook.setString(1, callnum);
	    	  insbook.setString(3, title);
	    	  insbook.setString(4, mainAuthor);
	    	  insbook.setString(5, publisher);
	    	  inscopy.setString (1, callnum);
	    	  inscopy.setString(3, "in");
	    	  inscopy.setInt(2, 1);
	    	  insbook.executeUpdate();
	    	  inscopy.executeUpdate();
	    	  for (int s =0; s<subject.length; s++){
	    		  inssubject.setString(1, callnum);
	    		  inssubject.setString(2, subject[s]);
	    		  inssubject.executeUpdate();
	    	  }

	    	  rs = stmt.executeQuery("SELECT * FROM BOOKCOPY WHERE CALLNUMBER="+"'"+ callnum +"'" +" AND COPYNO="+ 1 );
	    	  
	      }
	      else{
			  Statement ps2 = db.createStatement();
			  ResultSet rs3 = ps2.executeQuery("SELECT MAX(COPYNO) AS MAX FROM BOOKCOPY WHERE CALLNUMBER="+ "'" + callnum + "'");
			  int max =0;
			  while(rs3.next()){
				  max = rs3.getInt("MAX");  
			  }
			  rs3.close();
	    	  inscopy.setString(1, callnum);
	    	  inscopy.setString(3, "in");
	    	  inscopy.setInt(2, max+1);
	    	  inscopy.executeUpdate();  
	    	  rs = stmt.executeQuery("SELECT * FROM BOOKCOPY WHERE CALLNUMBER="+"'"+ callnum +"'" + " AND COPYNO=" + (max+1)); 
	      }      

		  db.commit();
		  inscopy.close();
		  insbook.close();
	}
	catch (SQLException ex)
	{
	}
	  return rs;
 }


 	/**
 	 * @param the oracle connection wrapper
 	 * @param the copy number
 	 * @param the call number of the book
 	 * @returns true if the book is deleted and false otherwise
 	 * @throws generic exception
 	 */ 
 public static boolean deleteBook(OracleDatabaseWrapper db, int copyNum, String callNum ) throws Exception
 {
	PreparedStatement  ps;	  
	connectionCheck(db);
	ps = db.createPreparedStatement("DELETE FROM bookcopy WHERE COPYNO =" +copyNum + " AND CALLNUMBER =" +"'" + callNum+ "'");
	int rowCount = ps.executeUpdate();
	db.commit();
	ps.close();
	if(rowCount ==0){
		return false;
	}

	return true;	 
 }
 
 	/**
 	 * @param the oracle connection wrapper
 	 * @param String representing the Borrower ID
 	 * @returns true if a borrower is deleted, false if the borrower doesn't exist or delete failed
 	 * @throws generic exception
 	 */ 
 public static boolean deleteBorrower(OracleDatabaseWrapper db, int ID) throws Exception
 {
	connectionCheck(db);
	PreparedStatement  ps;
	  ps = db.createPreparedStatement("DELETE FROM BORROWER WHERE BID=?");
	  ps.setInt(1, ID);
	  int rowCount = ps.executeUpdate();
	  db.commit();
	  ps.close();
	  if(rowCount ==0){
		  return false;
	  }

	return true;
 }
 
 	/**
 	 * @param String that is either "" or a subject (not case sensitive)
 	 * @param an oracle wrapper consistent with Borrower Actions
 	 * @returns a result set
 	 * @throws Exception
 	 */
 public static ResultSet generateCheckedOutReport(OracleDatabaseWrapper db, String subject) throws Exception{

	connectionCheck(db);
	 Statement smt;
	 ResultSet rs;
		smt = db.createStatement();
		if(subject.equalsIgnoreCase(" ")){
			rs = smt.executeQuery("SELECT CALLNUMBER, OUTDATE, OUTDATE+T.BOOKTIMELIMIT AS DUEDATE FROM BORROWING G, " +
				"BORROWER R, BORROWERTYPE T WHERE G.BID=R.BID AND G.INDATE IS NULL AND T.TYPE=R.TYPE ORDER BY CALLNUMBER");
		}
		else{
			PreparedStatement ps = db.createPreparedStatement("SELECT G.CALLNUMBER, OUTDATE, OUTDATE+T.BOOKTIMELIMIT AS DUEDATE FROM BORROWING G, " +
					"BORROWER R, BORROWERTYPE T, HASSUBJECT J WHERE G.BID=R.BID AND J.CALLNUMBER=G.CALLNUMBER AND (LOWER(J.SUBJECT) LIKE LOWER(?))" +
					 " AND G.INDATE IS NULL AND T.TYPE=R.TYPE ORDER BY G.CALLNUMBER");
			ps.setString(1, subject);
			 rs = ps.executeQuery();
		}
  	  return rs;
 }

 	/**
 	 * @param the db connection
 	 * @param the year of books required
 	 * @param the number of books to return
 	 * @return the result set containing the top n 
 	 * borrowed books for a given year ordered by number of times borrowed. Only displays
 	 * books returned by the end of that year
 	 * @throws some exception
 	 */ 
 public static ResultSet generatePopularReport(OracleDatabaseWrapper db, int year, int number) throws Exception
 {
		connectionCheck(db);
	String query = "SELECT * FROM (" +
			"SELECT B.CALLNUMBER, B.TITLE, B.MAINAUTHOR, COUNT(*) AS BORROWINGCOUNT " +
			"FROM BORROWING G, BOOK B " +
			"WHERE G.INDATE <= (TO_DATE('31-DEC-" + year +"'" + ", 'DD-MON-YYYY'))  AND " +
			"G.OUTDATE >= (TO_DATE('1-JAN-" + year +"'"+ ", 'DD-MON-YYYY')) " +
			"AND G.CALLNUMBER = B.CALLNUMBER " +
			"GROUP BY B.CALLNUMBER, B.MAINAUTHOR, B.TITLE ORDER BY BORROWINGCOUNT DESC) WHERE ROWNUM <=?";
	PreparedStatement ps = db.createPreparedStatement(query);
	ps.setInt(1, number);
	ResultSet rs = ps.executeQuery();
	return rs;
 }



}

 